-- extended event session to detect query timeouts
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'QueryTimeouts')
	DROP EVENT SESSION QueryTimeouts ON SERVER;
GO

CREATE EVENT SESSION QueryTimeouts ON SERVER
ADD EVENT sqlserver.attention(
	ACTION
	(
		sqlserver.client_app_name,
		sqlserver.client_hostname,
		sqlserver.database_id,
		sqlserver.database_name,
		sqlserver.is_system,
		sqlserver.nt_username,
		sqlserver.server_principal_name,
        sqlserver.sql_text,
		sqlserver.username
	)
	WHERE (sqlserver.is_system = 0 AND duration > 1000)
)
ADD TARGET package0.event_file
(
	SET FILENAME = N'C:\Temp\QueryTimeouts.xel',
		MAX_FILE_SIZE = 200, MAX_ROLLOVER_FILES = 5
);
GO

-- enable event session
ALTER EVENT SESSION QueryTimeouts ON SERVER STATE = START;
GO

-- monitor the workload

-- stop the event session
ALTER EVENT SESSION QueryTimeouts ON SERVER STATE = STOP;
GO

-- query target data
SELECT 
	event_data.value('(event/@timestamp)[1]','datetime2') AS 'timestamp',
	event_data.value('(event/action[@name="database_name"])[1]', 'nvarchar(128)') AS database_name,
	CAST(event_data.value('(event/data[@name="duration"])[1]','int')/1000.0 AS decimal(12,2)) AS duration_ms,
	event_data.value('(event/action[@name="client_hostname"])[1]', 'nvarchar(128)') AS client_hostname,
	event_data.value('(event/action[@name="client_app_name"])[1]', 'nvarchar(128)') AS client_app_name,
	event_data.value('(event/action[@name="nt_username"])[1]', 'nvarchar(128)') AS nt_user_name,
	event_data.value('(event/action[@name="user_name"])[1]', 'nvarchar(128)') AS user_name,
	event_data.value('(event/action[@name="server_principal_name"])[1]', 'nvarchar(128)') AS server_principal_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM
(
	SELECT
		CAST(event_data AS XML) AS event_data
	FROM sys.fn_xe_file_target_read_file
	(N'C:\Temp\QueryTimeouts*xel', NULL, NULL, NULL)
) AS tab;
GO

-- cleanup
DROP EVENT SESSION QueryTimeouts ON SERVER;
GO